/*DONE* Table [dbo].[<CompanyName>$Hotel Res_ Entry$7ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 14.1.2021 - JI ******/
/*Add prestaging table for one specific company*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyID as INT

SET @CompanyID = X --The X is the value that needs to be replaced with the [Company] ID from LSInsight$Companies
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @CompanyID)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].[' + @Company + '$Hotel Res_ Entry$7ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC (@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].[' + @Company + '$Hotel Res_ Entry$7ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[Entry No_] [int] IDENTITY(1,1) NOT NULL,
	[Reservation No_] [nvarchar](20) NOT NULL,
	[Date] [datetime] NOT NULL,
	[Group Reservation No_] [nvarchar](20) NOT NULL,
	[Room Type] [nvarchar](20) NOT NULL,
	[No_ of Rooms] [int] NOT NULL,
	[Rate Code] [nvarchar](20) NOT NULL,
	[Status] [int] NOT NULL,
	[Arrival Date] [datetime] NOT NULL,
	[Departure Date] [datetime] NOT NULL,
	[Room No_] [nvarchar](20) NOT NULL,
	[Billing Room Type] [nvarchar](20) NOT NULL,
	[Rate] [decimal](38, 20) NOT NULL,
	[Amount] [decimal](38, 20) NOT NULL,
	[Breakfast Code] [nvarchar](20) NOT NULL,
	[Amount (LCY)] [decimal](38, 20) NOT NULL,
	[Pax] [int] NOT NULL,
	[Complimentary] [tinyint] NOT NULL,
	[House Use] [tinyint] NOT NULL,
	[Reservation Type] [nvarchar](20) NOT NULL,
	[Adults] [int] NOT NULL,
	[Children] [int] NOT NULL,
	[Property] [nvarchar](20) NOT NULL,
	[Building] [nvarchar](20) NOT NULL,
	[Guest Name] [nvarchar](100) NOT NULL,
	[Country] [nvarchar](20) NOT NULL,
	[Segment] [nvarchar](20) NOT NULL,
	[Rate Changed] [tinyint] NOT NULL,
	[Allotment No_] [nvarchar](20) NOT NULL,
	[Allotment Line No_] [int] NOT NULL,
	[Revenue Entry No_] [int] NOT NULL,
	[Reservation Channel] [nvarchar](20) NOT NULL,
	[Currency] [nvarchar](10) NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[$systemCreatedAt] [datetime] NOT NULL,
	[$systemCreatedBy] [uniqueidentifier] NOT NULL,
	[$systemModifiedAt] [datetime] NOT NULL,
	[$systemModifiedBy] [uniqueidentifier] NOT NULL,
 CONSTRAINT [' + @Company + '$Hotel Res_ Entry$7ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[Entry No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [' + @Company + '$Hotel Res_ Entry$7ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END